<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>约束 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/100.245d2d1d.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/98e681/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" aria-current="page" class="active sidebar-link">约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_1-约束-constraint-概述" class="sidebar-link">1. 约束(constraint)概述</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_1-1-为什么需要约束" class="sidebar-link">1.1 为什么需要约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_1-2-什么是约束" class="sidebar-link">1.2 什么是约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_1-3-约束的分类" class="sidebar-link">1.3 约束的分类</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_2-非空约束" class="sidebar-link">2. 非空约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_2-1-作用" class="sidebar-link">2.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_2-2-关键字" class="sidebar-link">2.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_2-3-特点" class="sidebar-link">2.3 特点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_2-4-添加非空约束" class="sidebar-link">2.4 添加非空约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_2-5-删除非空约束" class="sidebar-link">2.5 删除非空约束</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_3-唯一性约束" class="sidebar-link">3. 唯一性约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-1-作用" class="sidebar-link">3.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-2-关键字" class="sidebar-link">3.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-3-特点" class="sidebar-link">3.3 特点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-4-添加唯一约束" class="sidebar-link">3.4 添加唯一约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-5-关于复合唯一约束" class="sidebar-link">3.5 关于复合唯一约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_3-5-删除唯一约束" class="sidebar-link">3.5 删除唯一约束</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_4-primary-key-约束" class="sidebar-link">4.PRIMARY KEY 约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-1-作用" class="sidebar-link">4.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-2-关键字" class="sidebar-link">4.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-3-特点" class="sidebar-link">4.3 特点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-4-添加主键约束" class="sidebar-link">4.4 添加主键约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-5-关于复合主键" class="sidebar-link">4.5 关于复合主键</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_4-6-删除主键约束" class="sidebar-link">4.6 删除主键约束</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_5-自增列-auto-increment" class="sidebar-link">5. 自增列：AUTO_INCREMENT</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-1-作用" class="sidebar-link">5.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-2-关键字" class="sidebar-link">5.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-3-特点和要求" class="sidebar-link">5.3 特点和要求</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-4-如何指定自增约束" class="sidebar-link">5.4 如何指定自增约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-5-如何删除自增约束" class="sidebar-link">5.5 如何删除自增约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_5-6-mysql-8-0新特性-自增变量的持久化" class="sidebar-link">5.6 MySQL 8.0新特性—自增变量的持久化</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_6-foreign-key-约束" class="sidebar-link">6. FOREIGN KEY 约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-1-作用" class="sidebar-link">6.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-2-关键字" class="sidebar-link">6.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-3-主表和从表-父表和子表" class="sidebar-link">6.3 主表和从表/父表和子表</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-4-特点" class="sidebar-link">6.4 特点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-5-添加外键约束" class="sidebar-link">6.5 添加外键约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-6-演示问题" class="sidebar-link">6.6 演示问题</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-7-约束等级" class="sidebar-link">6.7 约束等级</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-8-删除外键约束" class="sidebar-link">6.8 删除外键约束</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-9-开发场景" class="sidebar-link">6.9 开发场景</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_6-10-阿里开发规范" class="sidebar-link">6.10 阿里开发规范</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_7-check-约束" class="sidebar-link">7. CHECK 约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_7-1-作用" class="sidebar-link">7.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_7-2、关键字" class="sidebar-link">7.2、关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_7-3、说明-mysql-5-7-不支持" class="sidebar-link">7.3、说明：MySQL 5.7 不支持</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_8-default约束" class="sidebar-link">8. DEFAULT约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_8-1-作用" class="sidebar-link">8.1 作用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_8-2-关键字" class="sidebar-link">8.2 关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_8-3-如何给字段加默认值" class="sidebar-link">8.3 如何给字段加默认值</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/98e681/#_8-4-如何删除默认值约束" class="sidebar-link">8.4 如何删除默认值约束</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/98e681/#_9-面试" class="sidebar-link">9.面试</a></li></ul></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">约束<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="约束"><a href="#约束" class="header-anchor">#</a> 约束</h1> <h2 id="_1-约束-constraint-概述"><a href="#_1-约束-constraint-概述" class="header-anchor">#</a> 1. 约束(constraint)概述</h2> <h3 id="_1-1-为什么需要约束"><a href="#_1-1-为什么需要约束" class="header-anchor">#</a> 1.1 为什么需要约束</h3> <p>数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。</p> <p>为了保证数据的完整性，SQL规范以约束的方式对<strong>表数据进行额外的条件限制</strong>。从以下四个方面考虑：</p> <ul><li><code>实体完整性（Entity Integrity）</code> ：例如，同一个表中，不能存在两条完全相同无法区分的记录</li> <li><code>域完整性（Domain Integrity）</code> ：例如：年龄范围0-120，性别范围“男/女”</li> <li><code>引用完整性（Referential Integrity）</code>：例如：员工所在部门，在部门表中要能找到这个部门</li> <li><code>用户自定义完整性（User-defined Integrity）</code> ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。</li></ul> <h3 id="_1-2-什么是约束"><a href="#_1-2-什么是约束" class="header-anchor">#</a> 1.2 什么是约束</h3> <p>约束是表级的强制规定。
可以在<strong>创建表时规定约束（通过 CREATE TABLE 语句）</strong>，或者在<strong>表创建之后通过 ALTER TABLE 语句规定约束</strong>。</p> <h3 id="_1-3-约束的分类"><a href="#_1-3-约束的分类" class="header-anchor">#</a> 1.3 约束的分类</h3> <ul><li><strong>根据约束数据列的限制</strong>，约束可分为：
<ul><li><strong>单列约束</strong>：每个约束只约束一列</li> <li><strong>多列约束</strong>：每个约束可约束多列数据</li></ul></li> <li><strong>根据约束的作用范围</strong>，约束可分为：
<ul><li><strong>列级约束</strong>：只能作用在一个列上，跟在列的定义后面</li> <li><strong>表级约束</strong>：可以作用在多个列上，不与列一起，而是单独定义</li></ul></li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>			位置 					支持的约束类型 							是否可以起约束名
列级约束： 列的后面 			 语法都支持，但外键没有效果                           不可以
表级约束： 所有列的下面  		默认和非空不支持，其他支持  					    可以（主键没有效果）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li><strong>根据约束起的作用</strong>，约束可分为：
<ul><li><strong>NOT NULL 非空约束，规定某个字段不能为空</strong></li> <li><strong>UNIQUE 唯一约束，规定某个字段在整个表中是唯一的</strong></li> <li><strong>PRIMARY KEY 主键(非空且唯一)约束</strong></li> <li><strong>FOREIGN KEY 外键约束</strong></li> <li><strong>CHECK 检查约束</strong></li> <li><strong>DEFAULT 默认值约束</strong></li></ul></li></ul> <blockquote><p>注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果</p></blockquote> <p>查看某个表已有的约束</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#information_schema数据库名（系统库）</span>
<span class="token comment">#table_constraints表名称（专门存储各个表的约束）</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>table_constraints
<span class="token keyword">WHERE</span> table_name <span class="token operator">=</span> <span class="token string">'表名称'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="_2-非空约束"><a href="#_2-非空约束" class="header-anchor">#</a> 2. 非空约束</h2> <h3 id="_2-1-作用"><a href="#_2-1-作用" class="header-anchor">#</a> 2.1 作用</h3> <p>限定某个字段/某列的值不允许为空</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929350.png" alt="image-20220612220250383"></p> <h3 id="_2-2-关键字"><a href="#_2-2-关键字" class="header-anchor">#</a> 2.2 关键字</h3> <p>NOT NULL</p> <h3 id="_2-3-特点"><a href="#_2-3-特点" class="header-anchor">#</a> 2.3 特点</h3> <ul><li>默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型</li> <li>非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空</li> <li>一个表可以有很多列都分别限定了非空</li> <li>空字符串''不等于NULL，0也不等于NULL</li></ul> <h3 id="_2-4-添加非空约束"><a href="#_2-4-添加非空约束" class="header-anchor">#</a> 2.4 添加非空约束</h3> <p>（1）建表时</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型 <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> 
    字段名 数据类型 <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    sex <span class="token keyword">CHAR</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> student<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span><span class="token punctuation">,</span>
    sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">,</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token punctuation">,</span>
    cardid <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token string">'13710011002'</span><span class="token punctuation">,</span><span class="token string">'110222198912032545'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#成功</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token string">'13710011002'</span><span class="token punctuation">,</span><span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#身份证号为空</span>
ERROR <span class="token number">1048</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Column</span> <span class="token string">'cardid'</span> cannot be <span class="token boolean">null</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token boolean">null</span><span class="token punctuation">,</span><span class="token string">'110222198912032546'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功，tel允许为空</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token boolean">null</span><span class="token punctuation">,</span><span class="token boolean">null</span><span class="token punctuation">,</span><span class="token string">'110222198912032547'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#失败</span>
ERROR <span class="token number">1048</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Column</span> <span class="token string">'sname'</span> cannot be <span class="token boolean">null</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>（2）建表后</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp
<span class="token keyword">MODIFY</span> sex <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> student <span class="token keyword">modify</span> sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_2-5-删除非空约束"><a href="#_2-5-删除非空约束" class="header-anchor">#</a> 2.5 删除非空约束</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token boolean">NULL</span><span class="token punctuation">;</span><span class="token comment">#去掉not null，相当于修改某个非注解字段，该字段允许为空</span>

或

<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型<span class="token punctuation">;</span><span class="token comment">#去掉not null，相当于修改某个非注解字段，该字段允许为空</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp
<span class="token keyword">MODIFY</span> sex <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp
<span class="token keyword">MODIFY</span> NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'abc'</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="_3-唯一性约束"><a href="#_3-唯一性约束" class="header-anchor">#</a> 3. 唯一性约束</h2> <h3 id="_3-1-作用"><a href="#_3-1-作用" class="header-anchor">#</a> 3.1 作用</h3> <p>用来限制某个字段/某列的值不能重复。</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929351.png" alt="image-20220612220532536"></p> <h3 id="_3-2-关键字"><a href="#_3-2-关键字" class="header-anchor">#</a> 3.2 关键字</h3> <p>UNIQUE</p> <h3 id="_3-3-特点"><a href="#_3-3-特点" class="header-anchor">#</a> 3.3 特点</h3> <ul><li>同一个表可以有多个唯一约束。</li> <li>唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。</li> <li>唯一性约束允许列值为空。</li> <li>在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。</li> <li><strong>MySQL会给唯一约束的列上默认创建一个唯一索引。</strong></li></ul> <h3 id="_3-4-添加唯一约束"><a href="#_3-4-添加唯一约束" class="header-anchor">#</a> 3.4 添加唯一约束</h3> <p>（1）建表时</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型  <span class="token keyword">unique</span><span class="token punctuation">,</span> 
    字段名 数据类型  <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段名 数据类型
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型<span class="token punctuation">,</span> 
    字段名 数据类型<span class="token punctuation">,</span>
    <span class="token punctuation">[</span><span class="token keyword">constraint</span> 约束名<span class="token punctuation">]</span> <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> student<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span><span class="token punctuation">,</span>
    sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">unique</span><span class="token punctuation">,</span>
    cardid <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span> <span class="token keyword">unique</span> <span class="token keyword">key</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t_course<span class="token punctuation">(</span>
    cid <span class="token keyword">INT</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">,</span>
    cname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">,</span>
    description <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">USER</span><span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    PASSWORD <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token comment">-- 使用表级约束语法</span>
    <span class="token keyword">CONSTRAINT</span> uk_name_pwd <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>PASSWORD<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><blockquote><p>表示用户名和密码组合不能重复</p></blockquote> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token string">'13710011002'</span><span class="token punctuation">,</span><span class="token string">'101223199012015623'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token string">'13710011003'</span><span class="token punctuation">,</span><span class="token string">'101223199012015624'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token operator">|</span> sid <span class="token operator">|</span> sname <span class="token operator">|</span> tel         <span class="token operator">|</span> cardid             <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span> <span class="token number">13710011002</span> <span class="token operator">|</span> <span class="token number">101223199012015623</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span> <span class="token number">13710011003</span> <span class="token operator">|</span> <span class="token number">101223199012015624</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">,</span><span class="token string">'13710011004'</span><span class="token punctuation">,</span><span class="token string">'101223199012015624'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#身份证号重复</span>
ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span> entry <span class="token string">'101223199012015624'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'cardid'</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">,</span><span class="token string">'13710011003'</span><span class="token punctuation">,</span><span class="token string">'101223199012015625'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span> entry <span class="token string">'13710011003'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'tel'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>（2）建表后指定唯一键约束</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的</span>
<span class="token comment">#方式1：</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">add</span> <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段列表<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式2：</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 字段类型 <span class="token keyword">unique</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">USER</span>
<span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>PASSWORD<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">USER</span>
<span class="token keyword">ADD</span> <span class="token keyword">CONSTRAINT</span> uk_name_pwd <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>PASSWORD<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">USER</span>
<span class="token keyword">MODIFY</span> NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> student<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token punctuation">,</span>
    cardid <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> student <span class="token keyword">add</span> <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>tel<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> student <span class="token keyword">add</span> <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>cardid<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_3-5-关于复合唯一约束"><a href="#_3-5-关于复合唯一约束" class="header-anchor">#</a> 3.5 关于复合唯一约束</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型<span class="token punctuation">,</span> 
    字段名 数据类型<span class="token punctuation">,</span>
    <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段列表<span class="token punctuation">)</span> <span class="token comment">#字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多</span>
    个字段的组合是唯一的
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#学生表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> student<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#学号</span>
    sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">#姓名</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#电话</span>
    cardid <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span> <span class="token keyword">unique</span> <span class="token keyword">key</span> <span class="token comment">#身份证号</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#课程表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> course<span class="token punctuation">(</span>
    cid <span class="token keyword">int</span><span class="token punctuation">,</span>  <span class="token comment">#课程编号</span>
    cname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>   <span class="token comment">#课程名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#选课表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> student_course<span class="token punctuation">(</span>
    id <span class="token keyword">int</span><span class="token punctuation">,</span>
    sid <span class="token keyword">int</span><span class="token punctuation">,</span>
    cid <span class="token keyword">int</span><span class="token punctuation">,</span>
    score <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>sid<span class="token punctuation">,</span>cid<span class="token punctuation">)</span>  <span class="token comment">#复合唯一</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token string">'13710011002'</span><span class="token punctuation">,</span><span class="token string">'101223199012015623'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token string">'13710011003'</span><span class="token punctuation">,</span><span class="token string">'101223199012015624'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> course <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'Java'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token string">'MySQL'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token operator">|</span> sid <span class="token operator">|</span> sname <span class="token operator">|</span> tel         <span class="token operator">|</span> cardid             <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span> 张三   <span class="token operator">|</span> <span class="token number">13710011002</span> <span class="token operator">|</span> <span class="token number">101223199012015623</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span> 李四   <span class="token operator">|</span> <span class="token number">13710011003</span> <span class="token operator">|</span> <span class="token number">101223199012015624</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+-------------+--------------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> course<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token operator">|</span> cid  <span class="token operator">|</span> cname <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> Java  <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span> MySQL <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student_course <span class="token keyword">values</span>
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">,</span> <span class="token number">89</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1002</span><span class="token punctuation">,</span> <span class="token number">90</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">,</span> <span class="token number">88</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1002</span><span class="token punctuation">,</span> <span class="token number">56</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student_course<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+------+------+-------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> sid  <span class="token operator">|</span> cid <span class="token operator">|</span> score <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+------+-------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span>   <span class="token number">1</span>  <span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span>   <span class="token number">89</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span>   <span class="token number">1</span>  <span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span>   <span class="token number">90</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span>   <span class="token number">2</span>  <span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span>   <span class="token number">88</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">4</span> <span class="token operator">|</span>   <span class="token number">2</span>  <span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span>   <span class="token number">56</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student_course <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">,</span> <span class="token number">88</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#失败</span>

<span class="token comment">#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid'  违反sid-cid的复合唯一</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_3-5-删除唯一约束"><a href="#_3-5-删除唯一约束" class="header-anchor">#</a> 3.5 删除唯一约束</h3> <ul><li>添加唯一性约束的列上也会自动创建唯一索引。</li> <li>删除唯一约束只能通过删除唯一索引的方式删除。</li> <li>删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。</li> <li>如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>table_constraints <span class="token keyword">WHERE</span> table_name <span class="token operator">=</span> <span class="token string">'表名'</span><span class="token punctuation">;</span> <span class="token comment">#查看都有哪些约束</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">USER</span>
<span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> uk_name_pwd<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><blockquote><p>注意：可以通过  <code>show index from 表名称</code>; 查看表的索引</p></blockquote> <h2 id="_4-primary-key-约束"><a href="#_4-primary-key-约束" class="header-anchor">#</a> 4.PRIMARY KEY 约束</h2> <h3 id="_4-1-作用"><a href="#_4-1-作用" class="header-anchor">#</a> 4.1 作用</h3> <p>用来唯一标识表中的一行记录。</p> <h3 id="_4-2-关键字"><a href="#_4-2-关键字" class="header-anchor">#</a> 4.2 关键字</h3> <p>primary key</p> <h3 id="_4-3-特点"><a href="#_4-3-特点" class="header-anchor">#</a> 4.3 特点</h3> <ul><li>主键约束相当于<strong>唯一约束+非空约束的组合</strong>，主键约束列不允许重复，也不允许出现空值。</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929352.png" alt="image-20220612221208168"></p> <ul><li>一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。</li> <li>主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。</li> <li><strong>MySQL的主键名总是PRIMARY</strong>，就算自己命名了主键约束名也没用。</li> <li>当创建主键约束时，系统默认会在所在的列或列组合上建立对应的<strong>主键索引</strong>（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。</li> <li>需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。</li></ul> <h3 id="_4-4-添加主键约束"><a href="#_4-4-添加主键约束" class="header-anchor">#</a> 4.4 添加主键约束</h3> <p>（1）建表时指定主键约束</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型  <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#列级模式</span>
    字段名 数据类型<span class="token punctuation">,</span> 
    字段名 数据类型 
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型<span class="token punctuation">,</span> 
    字段名 数据类型<span class="token punctuation">,</span>
    <span class="token punctuation">[</span><span class="token keyword">constraint</span> 约束名<span class="token punctuation">]</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token punctuation">)</span> <span class="token comment">#表级模式</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> <span class="token keyword">temp</span><span class="token punctuation">(</span>
    id <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> <span class="token keyword">temp</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> id    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> name  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">temp</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> name <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span> 张三 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span> 李四 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#失败</span>
ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span>（重复） entry（键入，输入） <span class="token string">'1'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'PRIMARY'</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#失败</span>
ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span> entry <span class="token string">'1'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'PRIMARY'</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">temp</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> name <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span> 张三 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span> 李四 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span> 张三 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#成功</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">temp</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">,</span><span class="token string">'李琦'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#失败</span>
ERROR <span class="token number">1048</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Column</span> <span class="token string">'id'</span> cannot be <span class="token boolean">null</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">temp</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> name <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span> 张三 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span> 李四 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span> 张三 <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">4</span> <span class="token operator">|</span> <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#演示一个表建立两个主键约束</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> <span class="token keyword">temp</span><span class="token punctuation">(</span>
    id <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">primary</span> <span class="token keyword">key</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
ERROR <span class="token number">1068</span> <span class="token punctuation">(</span><span class="token number">42000</span><span class="token punctuation">)</span>: Multiple（多重的） <span class="token keyword">primary</span> <span class="token keyword">key</span> defined（定义）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>再举例：</p> <ul><li>列级约束</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp4<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li>表级约束</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp5<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    pwd <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">CONSTRAINT</span> emp5_id_pk <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>（2）建表后增加主键约束</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 表名称 <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>字段列表<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> student <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>sid<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp5 <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>pwd<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_4-5-关于复合主键"><a href="#_4-5-关于复合主键" class="header-anchor">#</a> 4.5 关于复合主键</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型<span class="token punctuation">,</span>
    字段名 数据类型<span class="token punctuation">,</span> 
    字段名 数据类型<span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token number">1</span><span class="token punctuation">,</span>字段名<span class="token number">2</span><span class="token punctuation">)</span>  <span class="token comment">#表示字段1和字段2的组合是唯一的，也可以有更多个字段</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#学生表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> student<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#学号</span>
    sname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>   <span class="token comment">#学生姓名</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#课程表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> course<span class="token punctuation">(</span>
    cid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#课程编号</span>
    cname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>   <span class="token comment">#课程名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#选课表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> student_course<span class="token punctuation">(</span>
    sid <span class="token keyword">int</span><span class="token punctuation">,</span>
    cid <span class="token keyword">int</span><span class="token punctuation">,</span>
    score <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">(</span>sid<span class="token punctuation">,</span>cid<span class="token punctuation">)</span>  <span class="token comment">#复合主键</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> course <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'Java'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token string">'MySQL'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+</span>
<span class="token operator">|</span> sid <span class="token operator">|</span> sname <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> course<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token operator">|</span> cid  <span class="token operator">|</span> cname <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> Java  <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span> MySQL <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student_course <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">,</span> <span class="token number">89</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token number">90</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token number">88</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token number">56</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student_course<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+------+-------+</span>
<span class="token operator">|</span> sid <span class="token operator">|</span> cid  <span class="token operator">|</span> score <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+------+-------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span>   <span class="token number">89</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span>   <span class="token number">90</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span>   <span class="token number">88</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span>   <span class="token number">56</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> student_course <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span> entry <span class="token string">'1-1001'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'PRIMARY'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> student_course<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------+---------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field <span class="token operator">|</span> <span class="token keyword">Type</span>    <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+---------+------+-----+---------+-------+</span>
<span class="token operator">|</span> sid   <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> cid   <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> score <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+---------+------+-----+---------+-------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>再举例</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp6<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    pwd <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">CONSTRAINT</span> emp7_pk <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>pwd<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h3 id="_4-6-删除主键约束"><a href="#_4-6-删除主键约束" class="header-anchor">#</a> 4.6 删除主键约束</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">drop</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> student <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp5 <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><blockquote><p>说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。</p></blockquote> <h2 id="_5-自增列-auto-increment"><a href="#_5-自增列-auto-increment" class="header-anchor">#</a> 5. 自增列：AUTO_INCREMENT</h2> <h3 id="_5-1-作用"><a href="#_5-1-作用" class="header-anchor">#</a> 5.1 作用</h3> <p>某个字段的值自增</p> <h3 id="_5-2-关键字"><a href="#_5-2-关键字" class="header-anchor">#</a> 5.2 关键字</h3> <p>auto_increment</p> <h3 id="_5-3-特点和要求"><a href="#_5-3-特点和要求" class="header-anchor">#</a> 5.3 特点和要求</h3> <p>（1）一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）
（4）自增约束的列的数据类型必须是整数类型
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。</p> <p>错误演示：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key </span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">unique</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类型</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_5-4-如何指定自增约束"><a href="#_5-4-如何指定自增约束" class="header-anchor">#</a> 5.4 如何指定自增约束</h3> <p>（1）建表时</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型  <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
    字段名 数据类型  <span class="token keyword">unique</span> <span class="token keyword">key</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">,</span> 
    字段名 数据类型  <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段名 数据类型  <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型 <span class="token keyword">default</span> 默认值 <span class="token punctuation">,</span>
    字段名 数据类型 <span class="token keyword">unique</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span> 
    字段名 数据类型 <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span><span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token operator">|</span> Field <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token operator">|</span> eid   <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span> <span class="token keyword">auto_increment</span> <span class="token operator">|</span>
<span class="token operator">|</span> ename <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>                <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>（2）建表后</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token keyword">auto_increment</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>例如：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>alter table employee modify eid int auto_increment;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token operator">|</span> Field <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra          <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token operator">|</span> eid   <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span> <span class="token keyword">auto_increment</span> <span class="token operator">|</span>
<span class="token operator">|</span> ename <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>                <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+----------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><h3 id="_5-5-如何删除自增约束"><a href="#_5-5-如何删除自增约束" class="header-anchor">#</a> 5.5 如何删除自增约束</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型<span class="token punctuation">;</span> <span class="token comment">#去掉auto_increment相当于删除</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> eid <span class="token keyword">int</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid   <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-------+-------------+------+-----+---------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><h3 id="_5-6-mysql-8-0新特性-自增变量的持久化"><a href="#_5-6-mysql-8-0新特性-自增变量的持久化" class="header-anchor">#</a> 5.6 MySQL 8.0新特性—自增变量的持久化</h3> <p>在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。 在MySQL 5.7版本中，测试步骤如下： 创建的数据表中包含自增主键的id字段，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test1<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>插入4个空值，执行如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>查询数据表test1中的数据，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span> id <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">4</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>删除id为4的记录，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> test1 <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>再次插入一个空值，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>查询此时数据表test1中的数据，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span> id <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">5</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>从结果可以看出，虽然删除了id为4的记录，但是再次插入空值时，并没有重用被删除的4，而是分配了
5。 删除id为5的记录，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> test1 <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>重启数据库</strong>，重新插入一个空值。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1 <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>再次查询数据表test1中的数据，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span> id <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">4</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>从结果可以看出，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。 在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个 <code>计数器</code> 来决定的，而该计数器只在 <code>内存中维护</code> ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。</p> <p>在MySQL 8.0版本中，上述测试步骤最后一步的结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span> id <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">6</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>从结果可以看出，自增变量已经持久化了。</p> <p>MySQL 8.0将自增主键的计数器持久化到 <code>重做日志</code> 中。每次计数器发生改变，都会将其写入重做日志
中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。</p> <h2 id="_6-foreign-key-约束"><a href="#_6-foreign-key-约束" class="header-anchor">#</a> 6. FOREIGN KEY 约束</h2> <h3 id="_6-1-作用"><a href="#_6-1-作用" class="header-anchor">#</a> 6.1 作用</h3> <p>限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929353.png" alt="image-20220612222209449"></p> <h3 id="_6-2-关键字"><a href="#_6-2-关键字" class="header-anchor">#</a> 6.2 关键字</h3> <p>FOREIGN KEY</p> <h3 id="_6-3-主表和从表-父表和子表"><a href="#_6-3-主表和从表-父表和子表" class="header-anchor">#</a> 6.3 主表和从表/父表和子表</h3> <p>主表（父表）：被引用的表，被参考的表</p> <p>从表（子表）：引用别人的表，参考别人的表</p> <p>例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。</p> <p>例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。</p> <h3 id="_6-4-特点"><a href="#_6-4-特点" class="header-anchor">#</a> 6.4 特点</h3> <p>（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列</p> <p>为什么？因为被依赖/被参考的值必须是唯一的</p> <p>（2）在创建外键约束时，如果不给外键约束命名，<strong>默认名不是列名，而是自动产生一个外键名</strong>（例如student_ibfk_1;），          也可以指定外键约束名。</p> <p>（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表</p> <p>（4）删表时，先删从表（或先删除外键约束），再删除主表</p> <p>（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖
该记录的数据，然后才可以删除主表的数据</p> <p>（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束</p> <p>（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类
型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
例如：都是表示部门编号，都是int类型。</p> <p>（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束
名。（根据外键查询效率很高）</p> <p>（9）删除外键约束后，必须 手动 删除对应的索引</p> <h3 id="_6-5-添加外键约束"><a href="#_6-5-添加外键约束" class="header-anchor">#</a> 6.5 添加外键约束</h3> <p>（1）建表时</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 主表名称<span class="token punctuation">(</span>
    字段<span class="token number">1</span> 数据类型  <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段<span class="token number">2</span> 数据类型
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> 从表名称<span class="token punctuation">(</span>
    字段<span class="token number">1</span> 数据类型  <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段<span class="token number">2</span> 数据类型<span class="token punctuation">,</span>
    <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token operator">&lt;</span>外键约束名称<span class="token operator">&gt;</span><span class="token punctuation">]</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span>（从表的某个字段<span class="token punctuation">)</span> <span class="token keyword">references</span> 主表名<span class="token punctuation">(</span>被参考字段<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样</span>
<span class="token comment">#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样</span>
<span class="token comment">-- FOREIGN KEY: 在表级指定子表中的列</span>
<span class="token comment">-- REFERENCES: 标示在父表中的列</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span> <span class="token comment">#主表</span>
	did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
	dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
                 
<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span><span class="token comment">#从表</span>
	eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
	ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
	deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
	<span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>  <span class="token comment">#在从表中指定外键约束</span>
	<span class="token comment">#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

说明：
（<span class="token number">1</span>）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（<span class="token number">2</span>）删除表时，先删除从表emp，再删除主表dept
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><p>（2）建表后
一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。</p> <p>格式：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 从表名 <span class="token keyword">ADD</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> 约束名<span class="token punctuation">]</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>从表的字段<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> 主表名<span class="token punctuation">(</span>被引用
字段<span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token keyword">on</span> <span class="token keyword">update</span> xx<span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token keyword">on</span> <span class="token keyword">delete</span> xx<span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp1
<span class="token keyword">ADD</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> emp_dept_id_fk<span class="token punctuation">]</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> dept<span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span> <span class="token comment">#员工所在的部门</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">#这两个表创建时，没有指定外键的话，那么创建顺序是随意</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> emp <span class="token keyword">add</span> <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_6-6-演示问题"><a href="#_6-6-演示问题" class="header-anchor">#</a> 6.6 演示问题</h3> <p>（1）失败：不是键列</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p>（2）失败：数据类型不一致</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">char</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是从表的deptid字段和主表的did字</span>
段的数据类型不一致，并且要它俩的逻辑意义一致
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>（3）成功，两个表字段名一样</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    did <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>did<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span> 
    <span class="token comment">#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号</span>
    <span class="token comment">#是否重名没问题，因为两个did在不同的表中</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>（4）添加、删除、修改问题</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span> 
    <span class="token comment">#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'教学部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span> <span class="token string">'财务部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#添加从表记录成功，在添加这条记录时，要求部门表有1001部门</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token number">1005</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">#添加从表记录失败</span>

ERROR <span class="token number">1452</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: Cannot <span class="token keyword">add</span>（添加） <span class="token operator">or</span> <span class="token keyword">update</span>（修改） a child <span class="token keyword">row</span>: a <span class="token keyword">foreign</span> <span class="token keyword">key</span>
<span class="token keyword">constraint</span> fails <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>atguigudb<span class="token punctuation">`</span></span><span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token keyword">CONSTRAINT</span> <span class="token identifier"><span class="token punctuation">`</span>emp_ibfk_1<span class="token punctuation">`</span></span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>deptid<span class="token punctuation">`</span></span><span class="token punctuation">)</span>
                  <span class="token keyword">REFERENCES</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>did<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">)</span> 从表emp添加记录失败，因为主表dept没有<span class="token number">1005</span>部门
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">update</span> emp <span class="token keyword">set</span> deptid <span class="token operator">=</span> <span class="token number">1002</span> <span class="token keyword">where</span> eid <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span><span class="token comment">#修改从表失败</span>
ERROR <span class="token number">1452</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: Cannot <span class="token keyword">add</span>（添加） <span class="token operator">or</span> <span class="token keyword">update</span>（修改） a child <span class="token keyword">row</span>（子表的记录）: a
<span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token keyword">constraint</span> fails（外键约束失败） <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>atguigudb<span class="token punctuation">`</span></span><span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token keyword">CONSTRAINT</span> <span class="token identifier"><span class="token punctuation">`</span>emp_ibfk_1<span class="token punctuation">`</span></span>
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>deptid<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>did<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">)</span>  <span class="token comment">#部门表did字段现在没有1002的值，所以员工表中不能修改员工所在部门deptid为1002</span>

<span class="token keyword">update</span> dept <span class="token keyword">set</span> did <span class="token operator">=</span> <span class="token number">1002</span> <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1001</span><span class="token punctuation">;</span><span class="token comment">#修改主表失败</span>
ERROR <span class="token number">1451</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: Cannot <span class="token keyword">delete</span>（删除） <span class="token operator">or</span> <span class="token keyword">update</span>（修改） a parent <span class="token keyword">row</span>（父表的记录）: a
<span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token keyword">constraint</span> fails <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>atguigudb<span class="token punctuation">`</span></span><span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token keyword">CONSTRAINT</span> <span class="token identifier"><span class="token punctuation">`</span>emp_ibfk_1<span class="token punctuation">`</span></span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span>
<span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>deptid<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>did<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token comment">#部门表did的1001字段已经被emp引用了，所以部门表的1001字段就不能修改了。</span>

<span class="token keyword">update</span> dept <span class="token keyword">set</span> did <span class="token operator">=</span> <span class="token number">1002</span> <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1003</span><span class="token punctuation">;</span><span class="token comment">#修改主表成功 因为部门表的1003部门没有被emp表引</span>
用，所以可以修改
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">delete</span> <span class="token keyword">from</span> dept <span class="token keyword">where</span> did<span class="token operator">=</span><span class="token number">1001</span><span class="token punctuation">;</span> <span class="token comment">#删除主表失败</span>
ERROR <span class="token number">1451</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: Cannot <span class="token keyword">delete</span>（删除） <span class="token operator">or</span> <span class="token keyword">update</span>（修改） a parent <span class="token keyword">row</span>（父表记录）: a
<span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token keyword">constraint</span> fails <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>atguigudb<span class="token punctuation">`</span></span><span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>emp<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token keyword">CONSTRAINT</span> <span class="token identifier"><span class="token punctuation">`</span>emp_ibfk_1<span class="token punctuation">`</span></span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span>
<span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>deptid<span class="token punctuation">`</span></span><span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token identifier"><span class="token punctuation">`</span>dept<span class="token punctuation">`</span></span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>did<span class="token punctuation">`</span></span><span class="token punctuation">)</span><span class="token punctuation">)</span>  <span class="token comment">#因为部门表did的1001字段已经被emp引用了，所以部门表的1001字段对应的记录就不能被删除</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>总结：约束关系是针对双方的</p> <ul><li>添加了外键约束后，主表的修改和删除数据受约束</li> <li>添加了外键约束后，从表的添加和修改数据受约束</li> <li>在从表上建立外键，要求主表必须存在</li> <li>删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除</li></ul> <h3 id="_6-7-约束等级"><a href="#_6-7-约束等级" class="header-anchor">#</a> 6.7 约束等级</h3> <ul><li><p><code>Cascade方式</code> ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录</p></li> <li><p><code>Set null方式</code> ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null</p></li> <li><p><code>No action方式</code> ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作</p></li> <li><p><code>Restrict方式</code> ：同no action， 都是立即检查外键约束</p></li> <li><p><code>Set default方式</code> （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置</p> <p>成一个默认的值，但Innodb不能识别</p></li></ul> <p>如果没有指定等级，就相当于Restrict方式。</p> <p>对于外键约束，最好是采用:  <code>ON UPDATE CASCADE ON DELETE RESTRICT</code> 的方式。</p> <p>（1）演示1：on update cascade on delete set null</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>  <span class="token keyword">on</span> <span class="token keyword">update</span> <span class="token keyword">cascade</span> <span class="token keyword">on</span> <span class="token keyword">delete</span> <span class="token keyword">set</span> <span class="token boolean">null</span>
    <span class="token comment">#把修改操作设置为级联修改等级，把删除操作设置为set null等级</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'教学部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span> <span class="token string">'财务部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span> <span class="token string">'咨询部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#在添加这条记录时，要求部门表有1001部门</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">,</span><span class="token number">1002</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">update</span> dept <span class="token keyword">set</span> did <span class="token operator">=</span> <span class="token number">1004</span> <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1002</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
<span class="token keyword">Rows</span> <span class="token keyword">matched</span>: <span class="token number">1</span>  Changed: <span class="token number">1</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span> <span class="token comment">#原来是1002，修改为1004</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token number">1004</span>  <span class="token operator">|</span> <span class="token comment">#原来是1002，跟着修改为1004</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#删除主表的记录成功，从表对应的字段的值被修改为null</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">delete</span> <span class="token keyword">from</span> dept <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1001</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did <span class="token operator">|</span> dname <span class="token operator">|</span> <span class="token comment">#记录1001部门被删除了</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三   <span class="token operator">|</span>  <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token comment">#原来引用1001部门的员工，deptid字段变为null</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四   <span class="token operator">|</span>  <span class="token boolean">NULL</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五   <span class="token operator">|</span>  <span class="token number">1004</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br></div></div><p>（2）演示2：on update set null on delete cascade</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>  <span class="token keyword">on</span> <span class="token keyword">update</span> <span class="token keyword">set</span> <span class="token boolean">null</span> <span class="token keyword">on</span> <span class="token keyword">delete</span> <span class="token keyword">cascade</span>
    <span class="token comment">#把修改操作设置为set null等级，把删除操作设置为级联删除等级</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'教学部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span> <span class="token string">'财务部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span> <span class="token string">'咨询部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#在添加这条记录时，要求部门表有1001部门</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">,</span><span class="token number">1002</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did <span class="token operator">|</span> dname <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token number">1002</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#修改主表，从表对应的字段设置为null</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">update</span> dept <span class="token keyword">set</span> did <span class="token operator">=</span> <span class="token number">1004</span> <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1002</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
<span class="token keyword">Rows</span> <span class="token keyword">matched</span>: <span class="token number">1</span>  Changed: <span class="token number">1</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span> <span class="token comment">#原来did是1002</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token boolean">NULL</span>  <span class="token operator">|</span> <span class="token comment">#原来deptid是1002，因为部门表1002被修改了，1002没有对应的了，就设置为null</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">delete</span> <span class="token keyword">from</span> dept <span class="token keyword">where</span> did<span class="token operator">=</span><span class="token number">1001</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span> <span class="token comment">#部门表中1001部门被删除</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span><span class="token comment">#原来1001部门的员工也被删除了</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token boolean">NULL</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br></div></div><p>（3）演示：on update cascade on delete cascade</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> dept<span class="token punctuation">(</span>
    did <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span> <span class="token comment">#部门编号</span>
    dname <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">#部门名称</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> emp<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>  <span class="token comment">#员工编号</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>   <span class="token comment">#员工姓名</span>
    deptid <span class="token keyword">int</span><span class="token punctuation">,</span> <span class="token comment">#员工所在的部门</span>
    <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>deptid<span class="token punctuation">)</span> <span class="token keyword">references</span> dept<span class="token punctuation">(</span>did<span class="token punctuation">)</span>  <span class="token keyword">on</span> <span class="token keyword">update</span> <span class="token keyword">cascade</span> <span class="token keyword">on</span> <span class="token keyword">delete</span> <span class="token keyword">cascade</span>
    <span class="token comment">#把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'教学部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span> <span class="token string">'财务部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> dept <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span> <span class="token string">'咨询部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#在添加这条记录时，要求部门表有1001部门</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'李四'</span><span class="token punctuation">,</span><span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> emp <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'王五'</span><span class="token punctuation">,</span><span class="token number">1002</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1002</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token number">1002</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#修改主表，从表对应的字段自动修改</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">update</span> dept <span class="token keyword">set</span> did <span class="token operator">=</span> <span class="token number">1004</span> <span class="token keyword">where</span> did <span class="token operator">=</span> <span class="token number">1002</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
<span class="token keyword">Rows</span> <span class="token keyword">matched</span>: <span class="token number">1</span>  Changed: <span class="token number">1</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1001</span> <span class="token operator">|</span> 教学部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span> <span class="token comment">#部门1002修改为1004</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 李四  <span class="token operator">|</span>  <span class="token number">1001</span>  <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token number">1004</span>  <span class="token operator">|</span> <span class="token comment">#级联修改</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">delete</span> <span class="token keyword">from</span> dept <span class="token keyword">where</span> did<span class="token operator">=</span><span class="token number">1001</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> dept<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> did  <span class="token operator">|</span> dname  <span class="token operator">|</span> <span class="token comment">#1001部门被删除了</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token operator">|</span> <span class="token number">1003</span> <span class="token operator">|</span> 咨询部 <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1004</span> <span class="token operator">|</span> 财务部 <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+--------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> deptid <span class="token operator">|</span>  <span class="token comment">#1001部门的员工也被删除了</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">3</span>  <span class="token operator">|</span> 王五  <span class="token operator">|</span>  <span class="token number">1004</span>  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><h3 id="_6-8-删除外键约束"><a href="#_6-8-删除外键约束" class="header-anchor">#</a> 6.8 删除外键约束</h3> <p>流程如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>第一步先查看约束名和删除外键约束
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>table_constraints <span class="token keyword">WHERE</span> table_name <span class="token operator">=</span> <span class="token string">'表名称'</span><span class="token punctuation">;</span><span class="token comment">#查看某个表的约束名</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 从表名 <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> 外键约束名<span class="token punctuation">;</span>

<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span>第二步查看索引名和删除索引。（注意，只能手动删除）
<span class="token keyword">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> 表名称<span class="token punctuation">;</span> <span class="token comment">#查看某个表的索引名</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 从表名 <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> 索引名<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>table_constraints <span class="token keyword">WHERE</span> table_name <span class="token operator">=</span> <span class="token string">'emp'</span><span class="token punctuation">;</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">alter</span> <span class="token keyword">table</span> emp <span class="token keyword">drop</span> <span class="token keyword">foreign</span> <span class="token keyword">key</span> emp_ibfk_1<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.02</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span> Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> <span class="token keyword">index</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>

mysql<span class="token operator">&gt;</span> <span class="token keyword">alter</span> <span class="token keyword">table</span> emp <span class="token keyword">drop</span> <span class="token keyword">index</span> deptid<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">0</span> Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>

mysql<span class="token operator">&gt;</span>  <span class="token keyword">show</span> <span class="token keyword">index</span> <span class="token keyword">from</span> emp<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="_6-9-开发场景"><a href="#_6-9-开发场景" class="header-anchor">#</a> 6.9 开发场景</h3> <p><strong>问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？</strong>
答：不是的</p> <p><strong>问题2：建和不建外键约束有什么区别？</strong>
答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。</p> <p>不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的 <code>引用完整性</code> ，只能依 <code>靠程序员的自觉</code> ，或者是 <code>在Java程序中进行限定</code> 。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。</p> <p><strong>问题3：那么建和不建外键约束和查询有没有关系？</strong>
答：没有</p> <blockquote><p>在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会 <code>因为外键约束的系统开销而变得非常慢</code> 。所以， MySQL 允许你不使用系统自带的外键约束，在 <code>应用层面</code> 完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。</p></blockquote> <h3 id="_6-10-阿里开发规范"><a href="#_6-10-阿里开发规范" class="header-anchor">#</a> 6.10 阿里开发规范</h3> <p>【 <code>强制</code> 】不得使用外键与级联，一切外键概念必须在应用层解决。</p> <p>说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 <code>单机低并发</code> ，不适合 <code>分布式</code> 、 <code>高并发集群</code> ；级联更新是强阻塞，存在数据库 <code>更新风暴</code> 的风险；外键影响数据库的 <code>插入速度</code> 。</p> <h2 id="_7-check-约束"><a href="#_7-check-约束" class="header-anchor">#</a> 7. CHECK 约束</h2> <h3 id="_7-1-作用"><a href="#_7-1-作用" class="header-anchor">#</a> 7.1 作用</h3> <p>检查某个字段的值是否符号xx要求，一般指的是值的范围</p> <h3 id="_7-2、关键字"><a href="#_7-2、关键字" class="header-anchor">#</a> 7.2、关键字</h3> <p>CHECK</p> <h3 id="_7-3、说明-mysql-5-7-不支持"><a href="#_7-3、说明-mysql-5-7-不支持" class="header-anchor">#</a> 7.3、说明：MySQL 5.7 不支持</h3> <p>MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告</p> <p>但是<strong>MySQL 8.0中可以使用check约束了。</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    gender <span class="token keyword">char</span> <span class="token keyword">check</span> <span class="token punctuation">(</span><span class="token string">'男'</span> <span class="token operator">or</span> <span class="token string">'女'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> employee <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">,</span><span class="token string">'妖'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> gender <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 张三  <span class="token operator">|</span> 妖     <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>再举例</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">temp</span><span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    age <span class="token keyword">INT</span> <span class="token keyword">CHECK</span><span class="token punctuation">(</span>age <span class="token operator">&gt;</span> <span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>再举例</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>age <span class="token keyword">tinyint</span> <span class="token keyword">check</span><span class="token punctuation">(</span>age <span class="token operator">&gt;</span><span class="token number">20</span><span class="token punctuation">)</span> 或 sex <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">check</span><span class="token punctuation">(</span>sex <span class="token operator">in</span><span class="token punctuation">(</span>‘男’<span class="token punctuation">,</span>’女’<span class="token punctuation">)</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>再举例</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CHECK</span><span class="token punctuation">(</span>height<span class="token operator">&gt;=</span><span class="token number">0</span> <span class="token operator">AND</span> height<span class="token operator">&lt;</span><span class="token number">3</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="_8-default约束"><a href="#_8-default约束" class="header-anchor">#</a> 8. DEFAULT约束</h2> <h3 id="_8-1-作用"><a href="#_8-1-作用" class="header-anchor">#</a> 8.1 作用</h3> <p>给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默
认值。</p> <h3 id="_8-2-关键字"><a href="#_8-2-关键字" class="header-anchor">#</a> 8.2 关键字</h3> <p>DEFAULT</p> <h3 id="_8-3-如何给字段加默认值"><a href="#_8-3-如何给字段加默认值" class="header-anchor">#</a> 8.3 如何给字段加默认值</h3> <p>（1）建表时</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型  <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段名 数据类型  <span class="token keyword">unique</span> <span class="token keyword">key</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">,</span> 
    字段名 数据类型  <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    字段名 数据类型  <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> 表名称<span class="token punctuation">(</span>
    字段名 数据类型 <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span>
    字段名 数据类型 <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span> 
    字段名 数据类型 <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> 默认值<span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">unique</span> <span class="token keyword">key</span><span class="token punctuation">(</span>字段名<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
说明：默认值约束一般不在唯一键和主键列上加
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">,</span>
    gender <span class="token keyword">char</span> <span class="token keyword">default</span> <span class="token string">'男'</span><span class="token punctuation">,</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">default</span> <span class="token string">''</span> <span class="token comment">#默认是空字符串</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field  <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> gender <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> 男      <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> tel    <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>    <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span>         <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> employee <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'汪飞'</span><span class="token punctuation">,</span><span class="token string">'男'</span><span class="token punctuation">,</span><span class="token string">'13700102535'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> gender <span class="token operator">|</span> tel         <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 汪飞  <span class="token operator">|</span> 男      <span class="token operator">|</span> <span class="token number">13700102535</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> employee<span class="token punctuation">(</span>eid<span class="token punctuation">,</span>ename<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'天琪'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token operator">|</span> eid <span class="token operator">|</span> ename <span class="token operator">|</span> gender <span class="token operator">|</span> tel         <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>  <span class="token operator">|</span> 汪飞  <span class="token operator">|</span> 男     <span class="token operator">|</span> <span class="token number">13700102535</span> <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span>  <span class="token operator">|</span> 天琪  <span class="token operator">|</span> 男     <span class="token operator">|</span>             <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----+-------+--------+-------------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">insert</span> <span class="token keyword">into</span> employee<span class="token punctuation">(</span>eid<span class="token punctuation">,</span>ename<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'二虎'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">#ERROR 1062 (23000): Duplicate entry '' for key 'tel' </span>
<span class="token comment">#如果tel有唯一性约束的话会报错，如果tel没有唯一性约束，可以添加成功</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>再举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> myemp<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    salary <span class="token keyword">DOUBLE</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token number">2000</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>（2）建表后</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token keyword">default</span> 默认值<span class="token punctuation">;</span>

<span class="token comment">#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了</span>
<span class="token comment">#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token keyword">default</span> 默认值 <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    eid <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    ename <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    gender <span class="token keyword">char</span><span class="token punctuation">,</span>
    tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field  <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> gender <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> tel    <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>    <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> gender <span class="token keyword">char</span> <span class="token keyword">default</span> <span class="token string">'男'</span><span class="token punctuation">;</span>  <span class="token comment">#给gender字段增加默认值约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">default</span> <span class="token string">''</span><span class="token punctuation">;</span> <span class="token comment">#给tel字段增加默认值约束</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field  <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> gender <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> 男      <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> tel    <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>    <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span>         <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">default</span> <span class="token string">''</span>  <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span><span class="token comment">#给tel字段增加默认值约束，并保留非空约束</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field  <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> gender <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> 男      <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> tel    <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>    <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span>         <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><h3 id="_8-4-如何删除默认值约束"><a href="#_8-4-如何删除默认值约束" class="header-anchor">#</a> 8.4 如何删除默认值约束</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型 <span class="token punctuation">;</span><span class="token comment">#删除默认值约束，也不保留非空约束</span>

<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名称 <span class="token keyword">modify</span> 字段名 数据类型  <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span> <span class="token comment">#删除默认值约束，保留非空约束</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> gender <span class="token keyword">char</span><span class="token punctuation">;</span> <span class="token comment">#删除gender字段默认值约束，如果有非空约束，也一并删除</span>

<span class="token keyword">alter</span> <span class="token keyword">table</span> employee <span class="token keyword">modify</span> tel <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>  <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span><span class="token comment">#删除tel字段默认值约束，保留非空约束</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">desc</span> employee<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> Field  <span class="token operator">|</span> <span class="token keyword">Type</span>        <span class="token operator">|</span> <span class="token boolean">Null</span> <span class="token operator">|</span> <span class="token keyword">Key</span> <span class="token operator">|</span> <span class="token keyword">Default</span> <span class="token operator">|</span> Extra <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token operator">|</span> eid    <span class="token operator">|</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>     <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span> PRI <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> ename  <span class="token operator">|</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> gender <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>     <span class="token operator">|</span> YES  <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">|</span> tel    <span class="token operator">|</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>    <span class="token operator">|</span> <span class="token keyword">NO</span>   <span class="token operator">|</span>     <span class="token operator">|</span> <span class="token boolean">NULL</span>    <span class="token operator">|</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">--------+-------------+------+-----+---------+-------+</span>
<span class="token number">4</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><h2 id="_9-面试"><a href="#_9-面试" class="header-anchor">#</a> 9.面试</h2> <p><strong>面试1、为什么建表时，加 not null default '' 或 default 0</strong>
答：不想让表中出现null值。</p> <p><strong>面试2、为什么不想要 null 的值</strong>
答:</p> <p>（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default '' 或 default 0</p> <p><strong>面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？</strong></p> <p>在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。</p> <p><strong>面试4、并不是每个表都可以任意选择存储引擎？</strong></p> <p>外键约束（FOREIGN KEY）不能跨引擎使用。</p> <p>MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。</p></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/13.约束.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/9515c0/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">MySQL数据类型精讲</div></a> <a href="/noteslibrary/pages/4535e0/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">视图</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/9515c0/" class="prev">MySQL数据类型精讲</a></span> <span class="next"><a href="/noteslibrary/pages/4535e0/">视图</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/100.245d2d1d.js" defer></script>
  </body>
</html>
